/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/03/15
 */
package vn.smartware.co.server.dao.initialize;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.commons.Const;
import vn.smartware.co.framework.bean.AddressDTO;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.bean.MenuDTO;
import vn.smartware.co.framework.bean.ProgramDTO;
import vn.smartware.co.framework.bean.UserDTO;
import vn.smartware.co.framework.db.factory.SQLManager;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.framework.form.BaseForm;
import vn.smartware.co.java.net.commons.EncryptionUtils;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.sql.factory.AbstractInitializeSQLManager;

/**
 * @author thuctt
 *
 */
public class InitializeSQLManager  extends SQLManager implements AbstractInitializeSQLManager {

	private static Log log = LogFactory.getLog(InitializeSQLManager.class);
	
	/**
	 * get menu
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void getMenuSQL(LoginDTO user, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT DISTINCT ")
		
		.append(" MENU.MENU_ID, ")
		.append(" MENU.MENU_NAME, ")
		.append(" MENU.REMARK ")
		
		.append(" FROM MST_MENU MENU ")
		
		.append(" INNER JOIN MST_AUTH AUTH ON AUTH.MENU_ID = MENU.MENU_ID ")
		.append(" AND AUTH.DELETE_DATE IS NULL ")
		.append(" AND AUTH.USER_ID = ? ")
		
		.append(" WHERE MENU.DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, user.getUserId());
			log.info("USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * getAvailableMenuSQL
	 * @param conn
	 * @throws ServerException
	 */
	public void getAvailableMenuSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" MENU.MENU_ID, ")
		.append(" MENU.MENU_NAME, ")
		.append(" MENU.REMARK ")
		
		.append(" FROM MST_MENU MENU ")
		.append(" WHERE MENU.DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * get program list
	 * 
	 * @param menu
	 * @param dto 
	 * @param conn
	 * @throws ServerException
	 */
	public void getPrgListSQL(MenuDTO menu, LoginDTO dto, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT DISTINCT  ")
		
		.append(" PRG.PRG_ID, ")
		.append(" PRG.PRG_IDX, ")
		.append(" PRG.CLASS_ID, ")
		.append(" PRG.VN_NAME, ")
		.append(" PRG.EN_NAME, ")
		.append(" PRG.DISPLAY_DIVISION, ")
		.append(" AUTH.PRG_AUTH ")
		
		.append(" FROM MST_PRG PRG ")
		
		.append(" INNER JOIN MST_AUTH AUTH ON AUTH.PRG_ID = PRG.PRG_ID ")
		.append(" AND AUTH.DELETE_DATE IS NULL ")
		.append(" AND AUTH.USER_ID = ? ")
		.append(" AND AUTH.PRG_AUTH > 0 ")
		.append(" AND AUTH.MENU_ID = ? ")
		
		.append(" WHERE PRG.DELETE_DATE IS NULL ")
		.append(" ORDER BY PRG.PRG_IDX ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, dto.getUserId());
			log.info("USER_ID = " + dto.getUserId());
			
			ps.setInt(i++, menu.getMenuId());
			log.info("MENU_ID = " + menu.getMenuId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("InitializeSQLManager getPrgListSQL SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * get program list of current login user
	 * 
	 * @param userId 
	 * @param conn
	 * @throws ServerException
	 */
	public void getAuthProgramsSQL(String userId, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT DISTINCT  ")
		
		.append(" PRG.PRG_ID, ")
		.append(" PRG.MENU_ID, ")
		.append(" PRG.CLASS_ID, ")
		.append(" PRG.VN_NAME, ")
		.append(" PRG.EN_NAME, ")
		.append(" PRG.DISPLAY_DIVISION, ")
		.append(" CLS.NAME AS DISPLAY_DIVISION_NAME ")
		
		.append(" FROM MST_PRG PRG ")
		
		.append(" INNER JOIN MST_AUTH AUTH ON AUTH.PRG_ID = PRG.PRG_ID ")
		.append(" AND AUTH.DELETE_DATE IS NULL ")
		.append(" AND AUTH.USER_ID = ? ")
		.append(" AND AUTH.PRG_AUTH > 0 ")
		.append(" AND AUTH.PRG_ID > 0 ")
		
		.append(" INNER JOIN MST_USER USER ")
		.append(" ON USER.USER_ID = AUTH.USER_ID ")
		.append(" AND (USER.LOCK_OUT_DATE IS NULL OR USER.LOCK_OUT_DATE = '') ")
		.append(" AND USER.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN MST_SYSTEM_CONFIG CLS ON CLS.KEY1 = ? ")
		.append(" AND CLS.VALUE = PRG.DISPLAY_DIVISION ")
		
		.append(" WHERE PRG.DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, userId);
			log.info("USER_ID = " + userId);
			
			ps.setString(i++, Const.SYSTEM_CONFIG.PRG_DISPLAY_TYPE);
			log.info("KEY1 = " + Const.SYSTEM_CONFIG.PRG_DISPLAY_TYPE);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * getProgramsSQL
	 * @param form
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void getProgramsSQL(BaseForm form, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" PRG.PRG_ID, ")
		.append(" PRG.PRG_IDX, ")
		.append(" PRG.MENU_ID, ")
		.append(" PRG.CLASS_ID, ")
		.append(" PRG.VN_NAME, ")
		.append(" PRG.EN_NAME, ")
		.append(" PRG.DISPLAY_DIVISION, ")
		.append(" CLS.NAME AS DISPLAY_DIVISION_NAME ")
		
		.append(" FROM MST_PRG PRG ")
		
		.append(" LEFT OUTER JOIN MST_SYSTEM_CONFIG CLS ON CLS.KEY1 = ? ")
		.append(" AND CLS.VALUE = PRG.DISPLAY_DIVISION ")
		
		.append(" WHERE PRG.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getIdStart())) {
			buf.append(" AND PRG.PRG_ID >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getIdEnd())) {
			buf.append(" AND PRG.PRG_ID <= ? ");
		}
		
		buf.append(" ORDER BY PRG.MENU_ID, PRG.PRG_IDX, PRG.PRG_ID ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, Const.SYSTEM_CONFIG.PRG_DISPLAY_TYPE);
			log.info("KEY1 = " + Const.SYSTEM_CONFIG.PRG_DISPLAY_TYPE);
			
			if (!Utils.isNullEmpty(form.getIdStart())) {
				ps.setString(i++, form.getIdStart());
				log.info("PRG_ID >= " + form.getIdStart());
			}
			
			if (!Utils.isNullEmpty(form.getIdEnd())) {
				ps.setString(i++, form.getIdEnd());
				log.info("PRG_ID <= " + form.getIdEnd());
			}
						
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * register program
	 * @param prg
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void registerProgramSQL(ProgramDTO prg, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		
		if (prg.getMode().equals(Const.MODE_NEW)){//register new
			buf.append(" INSERT INTO MST_PRG ( ")
			.append(" MENU_ID, PRG_IDX, CLASS_ID, VN_NAME, ")
			.append(" EN_NAME, DISPLAY_DIVISION, ")
			.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
			.append(" ) VALUES ( ")
			.append(" ?, ?, ?, ?, ?, ?, ")
			.append(" ?, SYSDATE(), ?, SYSDATE() ")
			.append(" ) ")
			;
			
		}else if (prg.getMode().equals(Const.MODE_EDIT)){//update
			buf.append(" UPDATE MST_PRG SET ")
			.append(" MENU_ID = ?, ")
			.append(" PRG_IDX = ?, ")
			.append(" CLASS_ID = ?, ")
			.append(" VN_NAME = ?, ")
			.append(" EN_NAME = ?, ")
			.append(" DISPLAY_DIVISION = ?, ")
			.append(" UPDATE_USER_ID = ?, ")
			.append(" UPDATE_DATE = SYSDATE() ")
			.append(" WHERE PRG_ID = ? ")
			;
		}else if (prg.getMode().equals(Const.MODE_DEL)){
			buf.append(" DELETE FROM MST_PRG ")
			.append(" WHERE PRG_ID = ? ")
			;
		}
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (prg.getMode().equals(Const.MODE_NEW)){//register new
				ps.setInt(i++, prg.getMenuId());
				log.info("MENU_ID = " + prg.getMenuId());
				
				ps.setString(i++, prg.getIndex());
				log.info("PRG_IDX = " + prg.getIndex());
				
				ps.setString(i++, prg.getPrgClass());
				log.info("CLASS_ID = " + prg.getPrgClass());
			
				ps.setString(i++, prg.getVnName());
				log.info("VN_NAME = " + prg.getVnName());
				
				ps.setString(i++, prg.getEnName());
				log.info("EN_NAME = " + prg.getEnName());
				
				ps.setString(i++, prg.getDspDivision());
				log.info("DISPLAY_DIVISION = " + prg.getDspDivision());
				
				ps.setInt(i++, user.getUserId());
				log.info("INSERT_USER_ID = " + user.getUserId());
				
				ps.setInt(i++, user.getUserId());
				log.info("UPDATE_USER_ID = " + user.getUserId());
				
			}else if (prg.getMode().equals(Const.MODE_EDIT)) {//UPDATE
				ps.setInt(i++, prg.getMenuId());
				log.info("MENU_ID = " + prg.getMenuId());
				
				ps.setString(i++, prg.getIndex());
				log.info("PRG_IDX = " + prg.getIndex());
				
				ps.setString(i++, prg.getPrgClass());
				log.info("CLASS_ID = " + prg.getPrgClass());
				
				ps.setString(i++, prg.getVnName());
				log.info("VN_NAME = " + prg.getVnName());
				
				ps.setString(i++, prg.getEnName());
				log.info("EN_NAME = " + prg.getEnName());
				
				ps.setString(i++, prg.getDspDivision());
				log.info("DISPLAY_DIVISION = " + prg.getDspDivision());
				
				ps.setInt(i++, user.getUserId());
				log.info("UPDATE_USER_ID = " + user.getUserId());
				
				ps.setInt(i++, prg.getPrgId());
				log.info("PRG_ID = " + prg.getPrgId());
				
			} else if (prg.getMode().equals(Const.MODE_DEL)) {
				ps.setInt(i++, prg.getPrgId());
				log.info("PRG_ID = " + prg.getPrgId());
			}
						
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * get only menu list without authority
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void getMenuListWithoutAuthSQL(LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		.append(" MENU_ID, ")
		.append(" LANG_CD, ")
		.append(" MENU_NAME, ")
		.append(" REMARK, ")
		.append(" DATE_FORMAT(DELETE_DATE,'%Y/%m/%d') AS DELETE_DATE, ")
		.append(" DATE_FORMAT(INSERT_DATE,'%Y/%m/%d') AS INSERT_DATE ")
		
		.append(" FROM MST_MENU MENU ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * create new menu
	 * @param dto
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void createNewMenuSQL(MenuDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MST_MENU ( ")
		
		.append(" MENU_NAME, ")
		.append(" REMARK, ")
		.append(" INSERT_USER_ID, ")
		.append(" INSERT_DATE, ")
		.append(" UPDATE_USER_ID, ")
		.append(" UPDATE_DATE ")
		
		.append(" ) VALUES ( ")
		
		.append(" ?, ?, ?, SYSDATE(), ?, SYSDATE() ")
		.append(" ) ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, dto.getMenuNameProperty());
			log.info("MENU_NAME = " + dto.getMenuNameProperty());
		
			ps.setString(i++, dto.getRemark());
			log.info("REMARK = " + dto.getRemark());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("InitializeSQLManager createNewMenuSQL SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * get max menu ID
	 * @param conn
	 * @throws ServerException
	 */
	public void getMaxMenuIdSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(MENU_ID) AS MAX_ID ")
		
		.append(" FROM MST_MENU ")
		
//		.append(" WHERE DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("InitializeSQLManager getMaxMenuIdSQL SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * create menu authority
	 * @param dto
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void createMenuAuthoritySQL(MenuDTO dto, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MST_AUTH ( ")
		
		.append(" MENU_ID, ")
		.append(" PRG_ID, ")
		.append(" USER_ID, ")
		.append(" PRG_AUTH, ")
		.append(" INSERT_USER_ID, ")
		.append(" INSERT_DATE, ")
		.append(" UPDATE_USER_ID, ")
		.append(" UPDATE_DATE ")
		
		.append(" ) VALUES ( ")
		
		.append(" ?, 0, ?, 0, ?, SYSDATE(), ?, SYSDATE() ")
		.append(" ) ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, dto.getMenuId());
			log.info("MENU_ID = " + dto.getMenuId());
		
			ps.setInt(i++, user.getUserId());
			log.info("USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("InitializeSQLManager createMenuAuthoritySQL SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * update menu
	 * @param dto
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void updateMenuSQL(MenuDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_MENU SET ")
		
		.append(" MENU_NAME = ?, ")
		.append(" REMARK = ?, ")
		.append(" UPDATE_USER_ID = ?, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE MENU_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, dto.getMenuNameProperty());
			log.info("MENU_NAME = " + dto.getMenuNameProperty());
		
			ps.setString(i++, dto.getRemark());
			log.info("REMARK = " + dto.getRemark());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, dto.getMenuId());
			log.info("MENU_ID = " + dto.getMenuId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("InitializeSQLManager updateMenuSQL SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * delete menu
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteMenuSQL(MenuDTO dto, Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_MENU ")
		.append(" SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE MENU_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, dto.getMenuId());
			log.info("MENU_ID = " + dto.getMenuId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * deleteUserSQL
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteUserSQL(UserDTO dto, Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_USER ")
		.append(" SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE USER_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, dto.getUserId());
			log.info("USER_ID = " + dto.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * delete authority
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteAuthSQL(MenuDTO dto, Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_AUTH ")
		.append(" SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE MENU_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, dto.getMenuId());
			log.info("MENU_ID = " + dto.getMenuId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * deleteAuthOfUserSQL
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteAuthOfUserSQL(UserDTO dto, Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_AUTH ")
		.append(" SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE USER_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, dto.getUserId());
			log.info("USER_ID = " + dto.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * Clear all rows in table ACTIONLOG
	 * @param conn
	 * @throws ServerException
	 */
	public void clearLogSQL(Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" DELETE FROM ACTIONLOG ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * getComboInitItemsSQL
	 * @param key
	 * @param conn
	 * @throws ServerException
	 */
	public void getComboInitItemsSQL(String key, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT SEQ, VALUE1 ")
		.append(" FROM MST_SYSTEM_CONFIG ")
		.append(" WHERE KEY1 = ? ")
		;
		
		compileQuery(conn, buf.toString(), key);
	}

	/**
	 * getUsersSQL
	 * @param form
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void getUsersSQL(BaseForm form, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" MST_USER.USER_ID, ")
		.append(" MST_USER.USER_NAME, ")
		.append(" MST_USER.PASSWORD, ")
		.append(" MST_USER.LOCK_OUT_DATE, ")
//		.append(" USERINFO.USER_CD, ")
		.append(" MST_USER.FULL_NAME, ")
		.append(" MST_USER.ID_NUMBER, ")
		.append(" MST_USER.BIRTHDAY, ")
		.append(" MST_USER.JOB_CD, ")
		.append(" MST_SYSTEM_CONFIG.VALUE1 AS JOB_NAME, ")
		.append(" MST_USER.TEL, ")
		.append(" MST_USER.EMAIL, ")
		.append(" MST_ADDRESS.ADDRESS_ID, ")
		.append(" MST_ADDRESS.HOUSE_NO, ")
		.append(" MST_ADDRESS.STREET, ")
		.append(" MST_ADDRESS.WARD, ")
		.append(" MST_ADDRESS.DISTRICT, ")
		.append(" MST_ADDRESS.CITY, ")
		.append(" MST_ADDRESS.NATIONALITY ")
		
		.append(" FROM MST_USER ")
		
//		.append(" LEFT OUTER JOIN USERINFO ")
//		.append(" ON USERINFO.USER_ID = MST_USER.USER_ID ")
//		.append(" AND USERINFO.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN MST_ADDRESS ")
		.append(" ON MST_ADDRESS.ADDRESS_ID = MST_USER.ADDRESS_ID ")
		.append(" AND MST_ADDRESS.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN MST_SYSTEM_CONFIG ")
		.append(" ON MST_SYSTEM_CONFIG.KEY1 = ? ")
		.append(" AND MST_SYSTEM_CONFIG.SEQ = MST_USER.JOB_CD ")
		
		.append(" WHERE MST_USER.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getIdStart())) {
			buf.append(" AND MST_USER.USER_ID >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getIdEnd())) {
			buf.append(" AND MST_USER.USER_ID <= ? ");
		}
		
		buf.append(" ORDER BY MST_USER.USER_ID ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, Const.SYSTEM_CONFIG.JOB);
			log.info("KEY1 = " + Const.SYSTEM_CONFIG.JOB);
			
			if (!Utils.isNullEmpty(form.getIdStart())) {
				ps.setString(i++, form.getIdStart());
				log.info("USER_ID >= " + form.getIdStart());
			}
			
			if (!Utils.isNullEmpty(form.getIdEnd())) {
				ps.setString(i++, form.getIdEnd());
				log.info("USER_ID <= " + form.getIdEnd());
			}
						
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * register login info
	 * @param dto
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void registerLoginInfoSQL(UserDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MST_USER ( ")
		.append(" USER_ID, ")
		.append(" USER_NAME, ")
		.append(" PASSWORD, ")
		.append(" LOCK_OUT_DATE, ")
		.append(" INIT_PRG_ID, ")
		
		.append(" FULL_NAME, ")
		.append(" ID_NUMBER, ")
		.append(" BIRTHDAY, ")
		.append(" JOB_CD, ")
		.append(" TEL, ")
		.append(" EMAIL, ")
		.append(" ADDRESS_ID, ")
		
		.append(" INSERT_USER_ID, ")
		.append(" INSERT_DATE, ")
		.append(" UPDATE_USER_ID, ")
		.append(" UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" (SELECT IFNULL(MAX(USER_ID), 0) + 1 FROM MST_USER), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE()")
		.append(")")
		;
		
		List<Object> params = new ArrayList<Object>();
		params.add(dto.getUserName());
		params.add(EncryptionUtils.getInstance().encrypt(dto.getPassword()));
		params.add(Utils.parseSQLDate(dto.getLockoutDate()));
		params.add(dto.getInitPrgId());
		params.add(dto.getName());
		params.add(dto.getIdNumber());
		params.add(Utils.parseSQLDate(dto.getBirthday()));
		params.add(dto.getJobCd());
		params.add(dto.getTel());
		params.add(dto.getMail());
		params.add(dto.getAddress().getAddressCd());
		params.add(user.getUserId());
		params.add(user.getUserId());
		
		compileQuery(conn, buf.toString(), params);
	}
	
	/**
	 * update user
	 * @param dto
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void updateLoginInfoSQL(UserDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_USER SET ")
		
		.append(" USER_NAME = ?, ")
		.append(" PASSWORD = ?, ")
		.append(" LOCK_OUT_DATE = ?, ")
		.append(" INIT_PRG_ID = ?, ")
		
		.append(" FULL_NAME = ?, ")
		.append(" ID_NUMBER = ?, ")
		.append(" BIRTHDAY = ?, ")
		.append(" JOB_CD = ?, ")
		.append(" TEL = ?, ")
		.append(" EMAIL = ?, ")
		.append(" ADDRESS_ID = ?, ")
		
		.append(" UPDATE_USER_ID = ?, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE USER_ID = ? ")
		;
		
		List<Object> params = new ArrayList<Object>();
		params.add(dto.getUserName());
		params.add(EncryptionUtils.getInstance().encrypt(dto.getPassword()));
		params.add(Utils.parseSQLDate(dto.getLockoutDate()));
		params.add(dto.getInitPrgId());
		params.add(dto.getName());
		params.add(dto.getIdNumber());
		params.add(Utils.parseSQLDate(dto.getBirthday()));
		params.add(dto.getJobCd());
		params.add(dto.getTel());
		params.add(dto.getMail());
		params.add(dto.getAddress().getAddressCd());
		params.add(user.getUserId());
		params.add(dto.getUserId());
		
		compileQuery(conn, buf.toString(), params);
	}
	
	/**
	 * get max addressCD
	 * @param conn
	 * @throws ServerException
	 */
	public void getMaxAddressCdSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(ADDRESS_ID) AS MAX_CD ")
		
		.append(" FROM MST_ADDRESS ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * get max addressCD
	 * @param conn
	 * @throws ServerException
	 */
	public void getMaxUserIDSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(USER_ID) AS MAX_ID ")
		
		.append(" FROM MST_USER ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * insert address
	 * @param dto
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void insertAddressSQL(AddressDTO dto, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MST_ADDRESS ( ")
		.append(" ADDRESS_ID, ")
		.append(" HOUSE_NO, ")
		.append(" STREET, ")
		.append(" WARD, ")
		.append(" DISTRICT, ")
		.append(" CITY, ")
		.append(" NATIONALITY, ")
		.append(" INSERT_USER_ID, ")
		.append(" INSERT_DATE, ")
		.append(" UPDATE_USER_ID, ")
		.append(" UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" (SELECT IFNULL(MAX(ADDRESS_ID), 0) + 1 FROM mst_address), ?, ?, ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE()")
		.append(")")
		;
		
		List<Object> params = new ArrayList<Object>();
		params.add(dto.getHouseNo());
		params.add(dto.getStreet());
		params.add(dto.getWard());
		params.add(dto.getDistrict());
		params.add(dto.getCity());
		params.add(dto.getNationality());
		params.add(user.getUserId());
		params.add(user.getUserId());
		
		compileQuery(conn, buf.toString(), params);
	}

	/**
	 * update address info
	 * @param dto 
	 * @param user 
	 * @param conn 
	 * @throws ServerException 
	 */
	public void updateAddressSQL(AddressDTO dto, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_ADDRESS SET ")
		
		.append(" HOUSE_NO = ?, ")
		.append(" STREET = ?, ")
		.append(" WARD = ?, ")
		.append(" DISTRICT = ?, ")
		.append(" CITY = ?, ")
		.append(" NATIONALITY = ?, ")
		.append(" UPDATE_USER_ID = ?, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE ADDRESS_ID = ? ")
		;
		
		List<Object> params = new ArrayList<Object>();
		params.add(dto.getHouseNo());
		params.add(dto.getStreet());
		params.add(dto.getWard());
		params.add(dto.getDistrict());
		params.add(dto.getCity());
		params.add(dto.getNationality());
		params.add(user.getUserId());
		params.add(dto.getAddressCd());
		
		compileQuery(conn, buf.toString(), params);
	}

	/**
	 * registerAuthSQL
	 * @param prg
	 * @param userID
	 * @param conn
	 * @throws ServerException
	 */
	public void registerAuthSQL(ProgramDTO prg, String userID, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MST_AUTH ( ")
		.append(" MENU_ID, ")
		.append(" PRG_ID, ")
		.append(" USER_ID, ")
		.append(" PRG_AUTH, ")
		.append(" INSERT_USER_ID, ")
		.append(" INSERT_DATE, ")
		.append(" UPDATE_USER_ID, ")
		.append(" UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ")
		.append(" ?, ")
		.append(" ?, ")
		.append(" ?, ")
		.append(" 99999, ")
		.append(" SYSDATE(), ")
		.append(" 99999, ")
		.append(" SYSDATE() ")
		.append(" ) ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, prg.getMenuId());
			log.info("MENU_ID = " + prg.getMenuId());
			
			ps.setInt(i++, prg.getPrgId());
			log.info("PRG_ID = " + prg.getPrgId());
			
			ps.setString(i++, userID);
			log.info("USER_ID = " + userID);
			
			ps.setString(i++, Const.ALL_PERMISSION);
			log.info("PRG_AUTH = " + Const.ALL_PERMISSION);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * deleteAllPrgsOfUserSQL
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteAllPrgsOfUserSQL(BaseForm form, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" DELETE FROM MST_AUTH ")
		.append(" WHERE USER_ID = ? ")
		.append(" AND PRG_AUTH > 0")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUserID());
			log.info("USER_ID = " + form.getUserID());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * deleteAddressSQL
	 * @param addr
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void deleteAddressSQL(AddressDTO addr, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_ADDRESS SET ")
		
		.append(" DELETE_DATE = SYSDATE() ")
		
		.append(" WHERE ADDRESS_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, addr.getAddressCd());
			log.info("ADDRESS_ID = " + addr.getAddressCd());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
}
